Transform Data / Data Modeling
Click on the at the top of the question to download the zip file containing the word document that contains all the DAX expressions for the columns that need to be created. You may get an error if all the columns have not been completed. Some use the other ones in order to create their column, once you have all completed then you should have no errors.
Step 1: Remove Blank and Other from Offense_Code_Group, Remove year 2020 from Year, there is no data within this year.
a. Choose the drop down arrow on Offense_Code_Group and select to remove Blank (null) and Other. You will then go to the Year column and make sure the Year 2020 has been removed, I believe that it will be with the removal of the other 2. You will then Close and Apply in the top left corner.
Step 2: Create the 8 new columns that are needed. Use the word document that is provided. I will demonstrate how I added the Time of Day column to my model.
a. You will copy each DAX expression and then paste it into the new column you are creating.
Step 3: Verify all columns have been added. See screenshot below and make sure you model matches.
Step 4: Format the Time by Hour to 13:30 (hh:mm)
Step 5: Create the LastRefresh_Local table.
let
Source = #table(type table[LastRefresh=datetime], {{DateTime.LocalNow()}})
in
Source
Below is a link that will share some other ways that you can model data inside of Power BI.